package com.parq.cs.db;

import java.io.BufferedReader;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;


public class CSDBProxy {

	private Connection connection;
	private PreparedStatement [] stmts;

	public CSDBProxy()throws Exception{
		Class.forName("com.mysql.jdbc.Driver");
		connection=DriverManager.getConnection(DBConfig.WEB_SEARCH_DB_URL,DBConfig.WEB_SEARCH_DB_USERNAME,DBConfig.WEB_SEARCH_DB_PASSWORD);
		connection.createStatement().executeUpdate("set names 'utf8'");
		stmts=new PreparedStatement[2];
		stmts[0]=connection.prepareStatement("insert into search (query,searchresults) values (?,?);");
		stmts[1]=connection.prepareStatement("select searchresults from search where query=?;");
	}
	
	public String searchDB(String query)throws Exception{
		stmts[1].clearParameters();
		setString(stmts[1], 1, query);
		ResultSet rset=stmts[1].executeQuery();
		String res=null;
		if (rset.next()){
			res=rset.getString("searchresults");
		}
		
		if (res!=null){
			byte[] utf8Bytes = res.getBytes("UTF8");
			res= new String(utf8Bytes, "UTF8");
		}

		return res;
	}

	public void addToDB(String query,String resultsFeed)throws Exception{
		stmts[0].clearParameters();
		setString(stmts[0], 1, query);
		setString(stmts[0], 2, resultsFeed);
		stmts[0].executeUpdate();
	}
	
	public void close()throws Exception{
		this.connection.close();
	}
	
	public static void setString(PreparedStatement stmt,int index,String str)throws Exception{
		byte[] utf8Bytes = str.getBytes("UTF8");
	    str = new String(utf8Bytes, "UTF8");
	    str=str.replaceAll("'", "''");
	    stmt.setString(index, str);
	}

}
